
import pymysql

class Mysql(object):

    def __init__(self,host,user,password,db,port=3306,charset='utf8'):
        try:
            self.conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db, charset=charset)

        except Exception as e:
            print('数据连接失败',e)
            self.conn = False

    def create_table(self):
        if not self.conn:
            return
        cursor = self.conn.cursor()
        sql = '''
        create table if not exists decktop_bg_img (
        id int unsigned auto_increment primary key,
        img_name varchar(50) not null,
        img_data longblob not null
        )
        '''
        try:
            cursor.execute(sql)
            self.conn.commit()
            cursor.close()
            print('数据表创建成功')
        except Exception as e:
            print('数据表创建失败',e)

    def insert_table(self,img_name,img_data):
        if not self.conn:
            return
        cursor = self.conn.cursor()
        sql = 'insert into decktop_bg_img (img_name,img_data) values(%s,%s)'
        try:
            self.delete_table()
            cursor.execute(sql,(img_name,img_data))
            self.conn.commit()
            cursor.close()
            print(f'{img_name} ------ 写入成功')
        except Exception as e:
            print('插入数据失败',e)

    def delete_table(self):
        if not self.conn:
            return
        cursor = self.conn.cursor()
        try:
           cursor.execute('delete from decktop_bg_img')
           self.conn.commit()
           cursor.close()
        except Exception as e:
            print('清空数据表失败',e)

    def query_table(self):
        if not self.conn:
            return
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        sql = 'select img_name,img_data from decktop_bg_img'
        try:
            cursor.execute(sql)
            results = cursor.fetchall()
            cursor.close()
            return results
        except Exception as e:
            print('查询失败',e)